package com.yayan.xh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.yayan.xh.bean.IntegralBean;

public class IntegralDao extends ConnectionBase implements IIntegralDao {

	private Connection conn = null;
	
	private QueryRunner qRunner = null;
	
	private PreparedStatement ps = null;
	
	private ResultSet rsKey = null;
	
	@Override
	public Map<String, Object> getUserSale(Map<String, String> map) throws Exception {
		Map<String, Object> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select b.g_sale as sale from tb_integral a left join tb_grade b on a.g_grade=b.g_grade where a.shop_id=? and a.u_id=?";
			conn = getConn();
			result = qRunner.query(conn, sql, new MapHandler(), map.get("shopId"), map.get("userId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public List<Map<String, Object>> getIntegralById(Map<String, String> map) throws Exception {
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select i_total as total, i_remain as remain, g_grade as grade from tb_integral where shop_id=? and u_id=?";
			conn = getConn();
			result = qRunner.query(conn, sql, new MapListHandler(), map.get("shopId"), map.get("userId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public List<IntegralBean> getIntegralBack(Map<String, String> map) throws Exception {
		List<IntegralBean> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.*, b.u_nickname from tb_integral a left join tb_user b on a.u_id=b.u_id where a.shop_id=?";
			conn = getConn();
			result = qRunner.query(conn, sql, new BeanListHandler<IntegralBean>(IntegralBean.class), map.get("shopId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public IntegralBean getIntegralBackByUserId(Map<String, String> map) throws Exception {
		IntegralBean result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.*, b.u_nickname from tb_integral a left join tb_user b on a.u_id=b.u_id where a.shop_id=? and a.u_id=?";
			conn = getConn();
			result = qRunner.query(conn, sql, new BeanHandler<IntegralBean>(IntegralBean.class), map.get("shopId"), map.get("userId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public boolean subtractIntegral(Map<String, String> map) throws Exception {
		boolean f = false;
		try {
			String sql = "update tb_integral set i_remain=i_remain-" + map.get("integral") + " where shop_id=? and u_id=?";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("shopId"));
			ps.setObject(2, map.get("userId"));
			f = ps.executeUpdate() > 0 ? true : false;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return f;
	}

	@Override
	public boolean addIntegral(Map<String, String> map) throws Exception {
		boolean f = false;
		try {
			String updateIntegralSql = "update tb_integral set i_remain=i_remain+" + map.get("integral") + ", i_total=i_total+" + map.get("integral") + " where shop_id=? and u_id=?";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(updateIntegralSql);
			ps.setObject(1, map.get("shopId"));
			ps.setObject(2, map.get("userId"));
			boolean intf = ps.executeUpdate() > 0 ? true : false;
			
			if(intf) {
				String updateGradeSql = "update tb_integral set g_grade=("
						+ "select g_grade from (select g_grade from tb_grade where g_integral<=("
						+ "select i_total from ((select c.i_total from tb_integral c where c.u_id=?)) as i_total ) "
						+ "order by g_grade desc limit 0,1) as grade)"
						+ "where shop_id=? and u_id=?";
				
				ps = conn.prepareStatement(updateGradeSql);
				ps.setObject(1, map.get("userId"));
				ps.setObject(2, map.get("shopId"));
				ps.setObject(3, map.get("userId"));
				f = ps.executeUpdate() > 0 ? true : false;
			}
			
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return f;
	}

}
